package cn.edu.sxw.web.server.jdbcdemo;

import com.alibaba.fastjson.util.TypeUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * jdbc使用
 */
public class JdbcDemo {
    /**
     * 连接到数据库
     */
    public Connection connectDatabase() throws ClassNotFoundException {
        String jdbc_string="jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=utf8";
        Class.forName("com.mysql.cj.jdbc.Driver");
        try {
            Connection connection= DriverManager.getConnection(jdbc_string,"user","*****");
            System.out.println("数据库连接建立成功");
            return connection;
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败："+e.getMessage());
        }
        return null;
    }

    /**
     * 输出影片列表
     */
    public void getMoviesPrint(){
        //（1）定义SQL
        String sql="SELECT  a.movie_id,a.name,a.name_en,img,rating,length,movie_publish_date,movie_year FROM  movie_info a,movie_types b,type_info c WHERE " +
                " a.movie_id = b.movie_id AND b.type_id = c.type_id AND c.NAME = ? AND a.movie_year >= ? AND a.rating >? ";
        Connection connection=null;
        PreparedStatement statement=null;
        try {
            connection=this.connectDatabase();//（2）创建连接
            statement=connection.prepareStatement(sql);//（3）准备语句
            statement.setObject(1,"喜剧"); //3.1对sql语句中出现的参数进行赋值
            statement.setObject(2,2000);
            statement.setObject(3,8.0f);
            ResultSet resultSet=statement.executeQuery();//（4）执行语句
            ResultSetMetaData metaData=resultSet.getMetaData();//获取结果集的元数据:描述数据的数据
            Integer columnCount=metaData.getColumnCount();
            int row=0;//（5）输出结果：按行遍历
            while (resultSet.next()){
                System.out.println("\n读到了一行数据，当前第"+(++row)+"行:");
                for(int i=1;i<=columnCount;i++){
                    String value=resultSet.getString(i);
                    System.out.print(value+"\t");
                }
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //（6）关闭语句和连接
            try {
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 执行sql语句，组装成List<Map<String,Object>>类型
     * @return
     */
    public List<Map<String,Object>> getMovieList(){
        //（1）定义SQL
        String sql="SELECT " +
                " a.movie_id,a.name,a.name_en,img,rating,length,movie_publish_date,movie_year " +
                "FROM " +
                " movie_info a,movie_types b,type_info c " +
                "WHERE " +
                " a.movie_id = b.movie_id AND b.type_id = c.type_id AND c.NAME = ? AND a.movie_year >= ? AND a.rating >? ";
        Connection connection=null;
        PreparedStatement statement=null;
        //定义List<Map<String,Object>>结果集类型：resultList
        List<Map<String,Object>> result=new ArrayList<Map<String,Object>>();
        try{
            //（2）创建连接
            connection=this.connectDatabase();
            //（3）准备语句
            statement=connection.prepareStatement(sql);
            //3.1对sql语句中出现的参数进行赋值
            statement.setObject(1,"喜剧");
            statement.setObject(2,2000);
            statement.setObject(3,8.0f);
            //（4）执行语句
            ResultSet resultSet=statement.executeQuery();
            ResultSetMetaData metaData=resultSet.getMetaData();
            Integer columnCount=metaData.getColumnCount();
            //（2）按行解析查询到的数据
            while (resultSet.next()){
                //（3）按字段将单行数据组装为HashMap<String,Object>类型
                Map<String,Object> rowMap=new HashMap<String,Object>();
                for(int i=1;i<=columnCount;i++){
                    String columnName=metaData.getColumnName(i);
                    Object value=resultSet.getObject(i);
                    rowMap.put(columnName,value);
                }
                //（4）将单行数据HashMap添加到resultList列表中
                result.add(rowMap);
            }
        }catch(Exception exception){
            exception.printStackTrace();
        }finally {
            try {
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //（5）返回resultList
        return result;
    }

    /**
     * 执行sql语句，组装成List<MovieInfo>类型
     * @return
     */
    public List<MovieInfo> getMovies(){
        //（1）根据SQL返回结果集，定义数据对象结构：MovieInfo。
        //（2）使用前一步方法，获得到List<Map<String,Object>> sqlList。
        List<Map<String,Object>> sqlList=this.getMovieList();
        //（3）定义并初始化返回结果List<MoviInfo> movieList实例。
        List<MovieInfo> result=new ArrayList<MovieInfo>();
        //（4）遍历sqlList，按行将Map数据转化为MovieInfo。
        for(int i=0;i<sqlList.size();i++){
            //取出一行数据
            Map<String,Object> rowMap=sqlList.get(i);
            //构造成MovieInfo对象
            MovieInfo movieInfo=new MovieInfo();
            movieInfo.setMovieId(TypeUtils.castToLong(rowMap.get("movie_id")));
            movieInfo.setMovieImage(TypeUtils.castToString(rowMap.get("img")));
            movieInfo.setMovieLength(TypeUtils.castToInt(rowMap.get("length")));
            movieInfo.setMovieName(TypeUtils.castToString(rowMap.get("name")));
            movieInfo.setMovieRating(TypeUtils.castToFloat(rowMap.get("rating")));
            movieInfo.setMovieYear(TypeUtils.castToString(rowMap.get("movie_year")));
            movieInfo.setMovieEnglishName(TypeUtils.castToString(rowMap.get("name_en")));
            movieInfo.setMoviePublishDate(TypeUtils.castToString(rowMap.get("movie_publish_date")));
            //（5）将转化后的MovieInfo添加到result中。
            result.add(movieInfo);
        }
        //（6）返回result。
        return result;
    }

    /**
     * 创建影片
     */
    public boolean addMovie(){
        //将数据写入dto
        MovieInfo movieInfo=new MovieInfo();
        movieInfo.setMovieId(99902l);
        movieInfo.setMovieName("致我的陌生恋人");
        movieInfo.setMovieImage("http://img5.mtime.cn/mt/2022/03/16/111932.77038333_1280X720X2.jpg");
        movieInfo.setMovieEnglishName("Mon inconnue");
        movieInfo.setMovieLength(117);
        movieInfo.setMovieYear("2019");
        movieInfo.setMoviePublishDate("20220414");
        Connection connection=null;
        PreparedStatement statement=null;
        boolean result=false;
        try {
            //（1）获得连接
            connection=this.connectDatabase();
            String sql="insert into movie_info(movie_id,name,img,name_en,length,movie_year,movie_publish_date)" +
                    " values(?,?,?,?,?,?,?)";
            //（2）准备SQL语句
            statement=connection.prepareStatement(sql);
            statement.setObject(1,movieInfo.getMovieId());
            statement.setObject(2,movieInfo.getMovieName());
            statement.setObject(3,movieInfo.getMovieImage());
            statement.setObject(4,movieInfo.getMovieEnglishName());
            statement.setObject(5,movieInfo.getMovieLength());
            statement.setObject(6,movieInfo.getMovieYear());
            statement.setObject(7,movieInfo.getMoviePublishDate());
            //（3）执行新增/修改/删除SQL语句
            result=statement.executeUpdate()>0;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //（4）关闭语句和连接
            try {
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    /**
     * 更新影片评分
     * @param movieId 影片id
     * @param rating 新的评分值
     * @return
     */
    public boolean updateMovie(Long movieId,Float rating){
        String sql="update movie_info set rating=? where movie_id=?";
        Connection connection=null;
        PreparedStatement statement=null;
        boolean result=false;
        try {
            //1、创建连接
            connection=this.connectDatabase();
            //2、准备语句
            statement=connection.prepareStatement(sql);
            statement.setObject(1,rating);
            statement.setObject(2,movieId);
            //3、执行
            result=statement.executeUpdate()>0;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    public static void main(String [] args){
        JdbcDemo jdbcDemo=new JdbcDemo();
        try {
            boolean flag=jdbcDemo.updateMovie(99900l,9.0f);
            System.out.println("得到数据操作结果：\n"+flag);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
